﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;

namespace Utility
{
    public class ExcelDrowDownList
    {
        public static ExcelDrowDownList Instance = new ExcelDrowDownList();

        private ExcelDrowDownList() { }

        private HSSFWorkbook workbook;

        /// <summary>
        /// 初始化工作簿
        /// </summary>
        /// <param name="path"></param>
        private HSSFWorkbook InitWorkbook()
        {
            workbook = new HSSFWorkbook();

            return workbook;
        }

        private HSSFSheet InitSheet(HSSFWorkbook workbook, string sheetName)
        {
            return workbook.CreateSheet(sheetName);
        }

        private HSSFCell InitCell(HSSFRow row, int col, HSSFCellStyle style)
        {
            HSSFCell cell = row.CreateCell(col);

            if (style != null)
                cell.CellStyle = style;

            return cell;
        }

        public bool ExportToExcel(DataTable dt, string sheetName, string path)
        {
            try
            {
                HSSFWorkbook workbook = InitWorkbook();

                HSSFSheet sheet = InitSheet(workbook, sheetName);

                DataTableInsert(dt, workbook, sheet);

                SaveExcel(path);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private void DataTableInsert(DataTable dt, HSSFWorkbook workbook, HSSFSheet sheet)
        {
            for (int i = 0; i < dt.Rows.Count; ++i)
            {
                int col = 0;
                HSSFRow row = sheet.CreateRow(i);

                foreach (DataColumn dc in dt.Columns)
                {
                    string colName = dc.ColumnName;
                    if (colName.ToLower().IndexOf("data") >= 0)
                        continue;

                    if (dt.Columns.Contains(colName + "DATA"))
                    {
                        if (!string.IsNullOrEmpty(dt.Rows[i][colName + "DATA"].ToString()))
                        {
                            string[] ddlData = dt.Rows[i][colName + "DATA"].ToString().Split(',');
                            CellRangeAddressList regions = new CellRangeAddressList(i, i, col, col);
                            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(ddlData);
                            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
                            sheet.AddValidationData(dataValidate);
                        }
                    }

                    HSSFCell cell = InitCell(row, col, null);

                    cell.SetCellValue(dt.Rows[i][colName].ToString());

                    sheet.AutoSizeColumn(col++, true);
                    //sheet.SetColumnWidth(col++, dt.Rows[i][colName].ToString().Length * 256 * 3);
                }
            }
        }

        private void SaveExcel(string filePath)
        {
            FileStream file = null;

            filePath = filePath.Replace("\\", "/");
            string path = filePath.Substring(0, filePath.LastIndexOf("/") + 1);

            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            file = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            workbook.Write(file);
            file.Close();
        }
    }
}